Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Using Locks

Locks are usually handled transparently for the user and are created and released automatically when the transaction has been committed or rolled back. Even with this transparency, it is important for you to realize that the locks are held until the commit or rollback. Long-running ad-hoc transactions that are not committed can cause contention problems on the system if they are not addressed.


CAUTION:  Transactions that have not been committed or rolled back continue to hold locks. Don’t forget to commit or roll back your transactions.

Explicit Locking

Tables can be explicitly locked by using the LOCK TABLE command. With this command, you can manually lock and unlock tables in different modes. By manually locking tables, you can have a lot of flexibility over how the locks are done—but there is a danger.

Locking tables manually can result in locks that are held longer than necessary and a loss of flexibility for the application. Manual locking should be reserved for the most-experienced programmer and only under extreme conditions. I don’t recommend using manual locking at all.

The SELECT...FOR UPDATE Statement

The SELECT...FOR UPDATE statement allows you to explicitly lock a set of rows that you will be updating. This statement allows you to update several rows as a group and be assured that none of the rows can change until you have finished your transaction. In this manner, no other process can acquire a lock on one of the elements in your transaction until you finish with all of them. Using the SELECT...FOR UPDATE statement is much more efficient than locking the table explicitly.

Deadlocks

Deadlocks occur when two (or more) processes hold resources that the other one needs. Because neither of the processes will release its resource until it has received the other’s resource, neither can proceed. If a deadlock occurs, Oracle breaks the deadlock by forcing one or more of the processes to roll back and release the resources. By carefully designing your application to acquire locks in sequences, you can avoid deadlocks.

Review of Locking

Locking is an important part of a multiuser RDBMS system. Relying on Oracle’s internal processing to handle locking for you is usually quite efficient. Unless absolutely necessary, take advantage of Oracle’s row-locking features and avoid table locks. By relying on the internal features of Oracle, you can simplify application development. Of course, more sophisticated applications may have to perform manual-locking operations, but don’t do so unless absolutely necessary.

Array Processing

When inserting a large number of rows into a table, you may be able to improve performance by taking advantage of array processing. Array processing allows multiple values to be passed to Oracle in one statement. Array processing reduces the number of calls to Oracle and reduces network overhead because a large network packet is much more efficient to send than many small packets.

Consider the following embedded SQL statement:

INSERT INTO dogs
( dogname, age, breed, owner )
VALUES
( :d_name, :d_age, :d_breed, :d_owner );

If the variables bound to d_name, d_age, d_breed, and d_owner are simple variables, then Oracle executes one statement. If the variables bound to those names are arrays, the entire array is sent to Oracle for processing. The SQL loader takes advantage of array processing to load rows in the most efficient manner possible; so do the Import and Export utilities.

Using VARCHAR2 instead of CHAR

By using the VARCHAR2 data type instead of the CHAR data type, you can eliminate unnecessary blank padding. The VARCHAR2 variable type stores a variable-length string; CHAR has a fixed-length format. By using VARCHAR2 instead of CHAR, you save space in the database as well as network and CPU resources. Although the VARCHAR data type is also supported, its use is not recommended.

Summary

This chapter is intended to wrap up all the miscellaneous tips and suggestions concerning the tuning of SQL statements. The chapter was a collection of unrelated items that didn’t quite fit in any of the other chapters in Part IV of this book. Having finished this chapter, you should be familiar with the following topics:

  Sequences and when they are useful
  Join operations and how to improve their performance
  Array processing and how to take advantage of it
  Oracle locking methods and the difference between row-level locking and table-level locking
  The difference between the VARCHAR2 and CHAR data types

Having finished Part IV of this book, “Tuning SQL,” you should have a solid understanding of the importance of tuning your SQL statements and a better idea of how to do that.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.